<?php

//session_start();

function WhatsMyEMpID($userName, $userPassHash) {
    $conn = new mysqli('localhost', 'root', '', 'paradigmShift_DEV');
    if (mysqli_connect_errno()) {
//echo "connection failed";
        return null;
    } else {
//echo "connection successful<br>";
    }
    $sql = "SELECT * FROM Employees, account WHERE Employees.EmpID = account.EmpID";
    $result = $conn->query($sql);
    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            if ($row['accName'] == $userName) {
//echo "true";
                $conn->close();
                return $row['empID'];
                break;
            }
        }
    }
    $conn->close();
    echo "no ID found";
    return null;
}

function checkIfExsist($userName, $userPassHash) {

    $conn = new mysqli('localhost', 'root', '', 'paradigmShift_DEV');
    if (mysqli_connect_errno()) {
//echo "connection failed";
    } else {
//echo "connection successful<br>";
    }
    $empID = WhatsMyEMpID($userName, $userPassHash);
    $sql = "SELECT * FROM Employees, account WHERE Employees.EmpID = account.EmpID";
    $result = $conn->query($sql);
    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            if ($row['empID'] == $empID) {
                $conn->close();
                return true;
                break;
            }
        }
    }
    $conn->close();
    echo "false";
    return false;
}

function anthonyViewMySchedule($userName, $userPassHash) {
//returns sets of {start time, endtime, roleID, dateID}
    $conn = new mysqli('localhost', 'root', '', 'paradigmShift_DEV');
    if (mysqli_connect_errno()) {
//echo "connection failed";
    } else {
//echo "connection successful<br>";
    }
    $returnable = array();
    if (checkIfExsist($userName, $userPassHash) == true) {
        $sql = "SELECT * FROM Employees,RoleDate,Roles,Dates,account WHERE Employees.EmpID = RoleDate.EmpID AND account.EmpID = Employees.EmpID AND Roles.RoleID=RoleDate.RoleID AND Dates.dateID=RoleDate.dateID";
        $result = $conn->query($sql);

        if ($result->num_rows > 0) {
            while ($row = $result->fetch_assoc()) {

                if ($row['accName'] == $userName && $row['UPassword'] = $userPassHash) {
                    $returnable[count($returnable)] = $row['startTime'];
                    $returnable[count($returnable)] = $row['endTime'];
                    $returnable[count($returnable)] = $row['roleID'];
                    $returnable[count($returnable)] = $row['dateID'];
                }
            }
        }
    }
    return $returnable;
    $conn->close();
}

function anthonyReleaseShift($userName, $userPassHash, $shiftNotDesired) {
//return success code{}
    $conn = new mysqli('localhost', 'root', '', 'paradigmShift_DEV');
    if (mysqli_connect_errno()) {
//echo "connection failed";
        return "Connection failure";
    } else {
//echo "connection successful";
    }
    $shift = json_decode($shiftNotDesired, true);
    $sql = "SELECT * FROM RoleDate";
    $result = $conn->query($sql);
    $updater = "UPDATE RoleDate SET 
ShiftStatus = 'Available' WHERE 
`RoleID` = '$shift[RoleID]'
AND `dateID` = '$shift[dateID]'";
    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            if ($row['RoleID'] == $shift['RoleID'] && $row['dateID'] == $shift['dateID']) {
                $conn->query($updater);
                return "Update Successful : shift has been released";
            }
        }
    } else {
        echo "no Shifts scheduled ";
        return "Update Failed : Shift not released";
    }
    $conn->close();
}

function anthonyRequestShift($userName, $userPassHash, $desiredShift) {
//return result
//this is meant to send to a new table that holds all requests does not change the current status it still counts as available
    $conn = new mysqli('localhost', 'root', '', 'paradigmShift_DEV');
    if (mysqli_connect_errno()) {
//echo "connection failed";
        return "Update Unsuccessful : no connection available";
    } else {
//echo "connection successful";
    }
    $ID = WhatsMyEMpID($userName, $userPassHash);
    $shift = json_decode($desiredShift, true);
    $sql = "SELECT * FROM ProposedChange";
    $result = $conn->query($sql);
    $updater = "INSERT INTO  `paradigmShift_DEV`.`ProposedChange` (
`empID`
`RoleID` ,
`dateID` ,
`MyEmpID` ,
`MyRoleID`,
`MyDateID`
)
VALUES (
'$ID','$shift[RoleID]',  '$shift[dateID]',  '$ID',  '0', '0'
);";

//why do you need to insert your own date ID? or role ID ?
//it only makes sense if your swapping shifts not collecting
//put in 0 and 0 as a place holder

    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $conn->query($updater);
            return "Update successful : Your request Is being considered";
        }
    }
    return "Update Unsuccessful : Unable to send request";
    $conn->close();
}

function anthonyDisplayShiftsOffered($userName, $userPassHash) {
//returns available shifts sorted
    $conn = new mysqli('localhost', 'root', '', 'paradigmShift_DEV');
    if (mysqli_connect_errno()) {
//echo "connection failed";
    } else {
//echo "connection successful";
    }
    $returnable = array();
    $sql = "SELECT * FROM RoleDate,Roles,Dates WHERE RoleDate.RoleID=Roles.RoleID AND RoleDate.dateID = Dates.dateID ";
    $result = $conn->query($sql);
    if ($result->num_rows > 0) {
//echo "<br>The Market is Open!<br>";
        while ($row = $result->fetch_assoc()) {
            if ($row['shiftStatus'] != "Occupied") {
                $returnable[count($returnable)] = $row['datestamp'];
                $returnable[count($returnable)] = $row['startTime'];
                $returnable[count($returnable)] = $row['endTime'];
                $returnable[count($returnable)] = $row['roleName'];
//echo "<br>" . $row['datestamp']. "<br>".$row['RoleName'];
            }
        }
    } else {
        echo "No shifts available<br>";
        echo "0 results found ";
    }
    return $returnable;
    $conn->close();
}

?>